﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmmed : Form
    {
        public frmmed()
        {
            InitializeComponent();
        }
        int num;
        private void frmmed_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
          
        }
        private void showdata()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT * FROM madecine ";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();

            this.dgvmad.DataSource = ds.Tables[0];
            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);

            this.dgvmad.ColumnHeadersDefaultCellStyle = cs;
            this.dgvmad.Columns[0].HeaderText = "รหัสยา";
            this.dgvmad.Columns[1].HeaderText = "ชื่อยา";
            this.dgvmad.Columns[2].HeaderText = "สรรพคุณ";
            this.dgvmad.Columns[3].HeaderText = "จำนวนคงเหลือ";
            this.dgvmad.Columns[4].HeaderText = "หน่วยนับ";

            this.dgvmad.Columns[0].Width = 100;
            this.dgvmad.Columns[1].Width = 150;
            this.dgvmad.Columns[2].Width = 250;
            this.dgvmad.Columns[3].Width = 80;
            this.dgvmad.Columns[4].Width = 100;
        }
        private void Format()
        {
            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);

            this.dgvmad.ColumnHeadersDefaultCellStyle = cs;
            this.dgvmad.Columns[0].HeaderText = "รหัสยา";
            this.dgvmad.Columns[1].HeaderText = "ชื่อยา";
            this.dgvmad.Columns[2].HeaderText = "สรรพคุณ";
            this.dgvmad.Columns[3].HeaderText = "จำนวนคงเหลือ";
            this.dgvmad.Columns[4].HeaderText = "หน่วยนับ";

            this.dgvmad.Columns[0].Width = 100;
            this.dgvmad.Columns[1].Width = 150;
            this.dgvmad.Columns[2].Width = 250;
            this.dgvmad.Columns[3].Width = 80;
            this.dgvmad.Columns[4].Width = 100;
            dgvmad.Rows[dgvmad.Rows.Count - 1].Height = 0;

        }
        private void ShowMad()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "select * from madecine Where MEDCCD = '" + madID.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            odbcDA.Fill(ds, "madecine");
            num = ds.Tables["madecine"].Rows.Count;

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);

            this.dgvmad.ColumnHeadersDefaultCellStyle = cs;
            this.dgvmad.Columns[0].HeaderText = "รหัสยา";
            this.dgvmad.Columns[1].HeaderText = "ชื่อยา";
            this.dgvmad.Columns[2].HeaderText = "สรรพคุณ";
            this.dgvmad.Columns[3].HeaderText = "จำนวนคงเหลือ";
            this.dgvmad.Columns[4].HeaderText = "หน่วยนับ";

            this.dgvmad.Columns[0].Width = 100;
            this.dgvmad.Columns[1].Width = 150;
            this.dgvmad.Columns[2].Width = 250;
            this.dgvmad.Columns[3].Width = 80;
            this.dgvmad.Columns[4].Width = 100;
        }

        private void insert_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

           
                    string query = "insert into madecine(MEDCCD,MEDCNM,DIST,AMOUN,UNIT) values('" + madID.Text + "','" + madName.Text + "','" + dist.Text + "','"+amoun.Text+"','"+unit.SelectedValue+"')";
                   
                    SqlCommand cmd7 = new SqlCommand(query, Conn);
                    cmd7.ExecuteNonQuery();

                    string sql = "select * from madecine Where MEDCCD= '" + madID.Text + "' ";
                    SqlCommand cmd1 = new SqlCommand(sql, Conn);
                    SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                    DataSet ds = new DataSet();

                    //Conn.Open();
                    odbcDA.Fill(ds);
                    //cmd.ExecuteReader();


                    this.dgvmad.DataSource = ds.Tables[0];

                    DataGridViewCellStyle cs = new DataGridViewCellStyle();
                    cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);


                    this.dgvmad.ColumnHeadersDefaultCellStyle = cs;
                    this.dgvmad.Columns[0].HeaderText = "รหัสยา";
                    this.dgvmad.Columns[1].HeaderText = "ชื่อยา";
                    this.dgvmad.Columns[2].HeaderText = "สรรพคุณ";
                    this.dgvmad.Columns[3].HeaderText = "จำนวนคงเหลือ";
                    this.dgvmad.Columns[4].HeaderText = "หน่วยนับ";

                    this.dgvmad.Columns[0].Width = 100;
                    this.dgvmad.Columns[1].Width = 150;
                    this.dgvmad.Columns[2].Width = 250;
                    this.dgvmad.Columns[3].Width = 80; 
                    this.dgvmad.Columns[4].Width = 100;
        }

        private void update_Click(object sender, EventArgs e)
        {
             string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (madID.Text == "" || madName.Text == "" || dist.Text == ""||amoun.Text==""||unit.Text=="")
            {
                MessageBox.Show("กรุณาป้อนข้อมูลให้ครบ!!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Information);
                madID.Focus();
                madID.SelectAll();
                return;
            }
            string sqlupdate = "update madecine set MEDCCD='" + madID.Text + "',MEDCNM='" + madName.Text + "',DIST='" + dist.Text + "',AMOUN='" + amoun.Text + "', UNIT='" + unit.Text + "' where MEDCCD='" + madID.Text + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sqlupdate;
            cmd.Connection = Conn;
            cmd.ExecuteNonQuery();
            ShowMad();

            madID.Text = "";
            madName.Text = "";
            dist.Text = "";
            amoun.Text = "";
            unit.Text = "";
        }

        private void delete_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string query = "delete from madecine where MEDCCD='" + madID.Text + "'";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();

            
            madID.Text = "";
            madName.Text = "";
            dist.Text = "";
            amoun.Text = "";
            unit.Text = "";


        }

        private void button2_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT * FROM madecine ";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dgvmad.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);


            this.dgvmad.ColumnHeadersDefaultCellStyle = cs;
                    this.dgvmad.Columns[0].HeaderText = "รหัสยา";
                    this.dgvmad.Columns[1].HeaderText = "ชื่อยา";
                    this.dgvmad.Columns[2].HeaderText = "สรรพคุณ";
                    this.dgvmad.Columns[3].HeaderText = "จำนวนคงเหลือ";
                    this.dgvmad.Columns[4].HeaderText = "หน่วยนับ";

                    this.dgvmad.Columns[0].Width = 100;
                    this.dgvmad.Columns[1].Width = 150;
                    this.dgvmad.Columns[2].Width = 250;
                    this.dgvmad.Columns[3].Width = 80; 
                    this.dgvmad.Columns[4].Width = 100;
        
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void dgvmad_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            madID.Text = Convert.ToString(dgvmad.Rows[e.RowIndex].Cells[0].Value);
            madName.Text = Convert.ToString(dgvmad.Rows[e.RowIndex].Cells[1].Value);
            dist.Text = Convert.ToString(dgvmad.Rows[e.RowIndex].Cells[2].Value);
            amoun.Text = Convert.ToString(dgvmad.Rows[e.RowIndex].Cells[3].Value);
            unit.Text = Convert.ToString(dgvmad.Rows[e.RowIndex].Cells[4].Value);

        }

       
        }
    }

